package com.java2007.zhangyu.restaurant.dao.impl;

import com.java2007.zhangyu.restaurant.dao.TableDao;
import com.java2007.zhangyu.restaurant.pojo.DinnerTable;
import com.java2007.zhangyu.restaurant.utils.JdbcUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author zy
 * @Version
 * @date 2021/01/28
 */
public class TableDaoImpl implements TableDao {
    /**
     * 保存餐桌信息
     * @param dinnerTable
     * @return
     * @throws SQLException
     */
    @Override
    public int saveTable(DinnerTable dinnerTable) throws SQLException {
        QueryRunner runner = new QueryRunner();

        String sql = "INSERT INTO t_dinner_table(dt_Name,dt_state) VALUES(?,?)";

        return runner.update(JdbcUtils.getConnection(),sql,dinnerTable.getDtName(),dinnerTable.getDtState());
    }

    /**
     * 删除餐桌名
     * @param dtId 餐桌 id
     * @return
     * @throws Exception
     */
    @Override
    public int deleteByTableId(Integer dtId) throws Exception {
        QueryRunner runner = new QueryRunner();

        String sql = "DELETE FROM t_dinner_table WHERE dt_id = ?";

        return runner.update(JdbcUtils.getConnection(),sql,dtId);
    }

    /**
     * 修改餐桌状态
     * @param table
     * @return
     * @throws SQLException
     */
    @Override
    public int update(DinnerTable table) throws SQLException {
        QueryRunner runner = new QueryRunner();

        String sql = "UPDATE t_dinner_table SET dt_state = ?,dt_preset_time = ? WHERE dt_id = ?";

        return runner.update(JdbcUtils.getConnection(), sql,table.getDtState(),table.getDtPresetTime(),table.getDtId());

    }

    /**
     * 根据餐桌 id查询餐桌信息
     * @param dtId 餐桌 id
     * @return
     * @throws SQLException
     */
    @Override
    public DinnerTable queryByTableId(Integer dtId) throws SQLException {
        QueryRunner runner = new QueryRunner();

        String sql = "SELECT dt_id dtId, dt_name dtName,dt_state dtState,dt_preset_time dtPresetTime \n" +
                "FROM t_dinner_table \n" +
                "WHERE dt_id = ?";

        return runner.query(JdbcUtils.getConnection(),sql,new BeanHandler<>(DinnerTable.class),dtId);
    }

    /**
     * 根据餐桌名模糊查询餐桌信息
     * @param dtName 餐桌名
     * @return
     * @throws SQLException
     */
    @Override
    public List<DinnerTable> queryTableName(String dtName) throws SQLException {
        QueryRunner runner = new QueryRunner();

        String sql = "SELECT dt_id dtId, dt_name dtName,dt_state dtState,dt_preset_time dtPresetTime \n" +
                "FROM t_dinner_table \n" +
                "WHERE dt_name like ?";

       return runner.query(JdbcUtils.getConnection(),sql,new BeanListHandler<>(DinnerTable.class),dtName);
    }

    /**
     * 查询餐桌信息
     * @return
     * @throws Exception
     */
    @Override
    public List<DinnerTable> queryAll() throws Exception {
        QueryRunner runner = new QueryRunner();

        String sql = "SELECT dt_id dtId, dt_name dtName,dt_state dtState,dt_preset_time dtPresetTime \n" +
                "FROM t_dinner_table";

        return runner.query(JdbcUtils.getConnection(),sql,new BeanListHandler<>(DinnerTable.class));
    }

    /**
     * 查询餐桌信息
     * @param dtState
     * @return
     * @throws Exception
     */
    @Override
    public List<DinnerTable> queryByStatus(Integer dtState) throws Exception {
        QueryRunner runner = new QueryRunner();

        String sql = "SELECT dt_id dtId, dt_name dtName,dt_state dtState,dt_preset_time dtPresetTime \n" +
                "FROM t_dinner_table \n" +
                "WHERE dt_state like ?";

        return runner.query(JdbcUtils.getConnection(),sql,new BeanListHandler<>(DinnerTable.class),dtState);
    }

    @Override
    public DinnerTable selectTableByDtName(String dtName) throws Exception{
        QueryRunner runner = new QueryRunner();

        String sql = "SELECT dt_id dtId, dt_name dtName,dt_state dtState,dt_preset_time dtPresetTime \n" +
                "FROM t_dinner_table \n" +
                "WHERE dt_name = ?";

        return runner.query(JdbcUtils.getConnection(),sql,new BeanHandler<>(DinnerTable.class),dtName);
    }
}
